﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Common
{
    public class Ecommon
    {

        /// <summary>
        /// 添加SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="tablename"></param>
        /// <param name="PKName"></param>
        /// <returns></returns>
        public static string GetSql<T>(T t, string tablename, Dictionary<string, bool> PKName) where T : class
        {
            string sql = "insert into {0}({1})   values  ({2})";

            string field = "";
            string values = "";

            string Pk = PKName.Keys.First();
            bool ifPK = PKName.Values.First();
            foreach (var item in t.GetType().GetProperties())
            {
                if (ifPK)
                {
                    if (item.Name == Pk)
                        continue;
                }
                field += item.Name + ",";

                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(DateTime))
                {
                    values += "'" + item.GetValue(t) + "',";
                }
                else
                {
                    if (item.PropertyType == typeof(int) || item.PropertyType == typeof(decimal))
                    {
                        values += item.GetValue(t) + ",";
                    }
                }
            }
            field = field.TrimEnd(',');
            values = values.TrimEnd(',');
            return string.Format(sql, tablename, field, values);
        }

        /// <summary>
        /// 显示SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static string GetSqlList<T>(string tablename, params SqlParameter[] paramters) where T : class,new()
        {
            string sql = "select {0} from {1} ";
            if (paramters.Length <= 0)
            {
                T t = new T();
                string field = "";
                foreach (var item in t.GetType().GetProperties())
                {
                    field += item.Name + ",";
                }
                field = field.TrimEnd(',');
                return string.Format(sql, field, tablename);
            }
            else
            {
                sql += "where ";
                for (var i = 0; i < paramters.Length; i++)
                {
                    if (i == 0)
                    {
                        sql += " " + paramters[i].ParameterName + " ='" + paramters[i].Value + "'";

                    }
                    else
                    {
                        sql += " and  " + paramters[i].ParameterName + " ='" + paramters[i].Value + "'";

                    }
                }
                sql = sql.TrimEnd(',');
                T t = new T();
                string field = "";
                foreach (var item in t.GetType().GetProperties())
                {
                    field += item.Name + ",";
                }
                field = field.TrimEnd(',');

                return string.Format(sql, field, tablename);
            }

        }
        /// <summary>
        /// 删除SQL语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pkname"></param>
        /// <param name="tablename"></param>
        /// <param name="PKName"></param>
        /// <returns></returns>
        public static string DelSQL<T>(T t, string tablename, Dictionary<string, bool> PKName) where T : new()
        {
            //string sql = "delete  from {0}  where   {1}={2}";

            //return string.Format(sql, tablename, PKName.Keys.First(), pkname);
            string sql = "delete from {0} where {1}";


            string PK = PKName.Keys.First();


            string where = "";


            var PKItem = t.GetType().GetProperty(PK);
            if (PKItem.PropertyType == typeof(string) || PKItem.PropertyType == typeof(DateTime))
            {
                where = PK + "=" + "'" + PKItem.GetValue(t) + "'";
            }
            else
            {
                where = PK + "=" + "'" + PKItem.GetValue(t) + "'";
            }
            return string.Format(sql, tablename, where);
        }


        /// <summary>
        /// 反填sql语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PKName"></param>
        /// <param name="tablename"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string BackSQL<T>(Dictionary<string, bool> PKName, string tablename, int id) where T : new()
        {
            string sql = "select {0} from {1} where {2}={3}";
            T t = new T();
            string field = "";
            foreach (var item in t.GetType().GetProperties())
            {
                field += item.Name + ",";
            }
            field = field.TrimEnd(',');
            return string.Format(sql, field, tablename, PKName.Keys.First(), id);
        }

        /// <summary>
        /// 修改所有字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PKName"></param>
        /// <param name="tablename"></param>
        /// <param name="t"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static string UpdSQL<T>(Dictionary<string, bool> PKName, string tablename, T t, int id)
        {
            string sql = "update {0} set {1} where {2}={3}";
            string field = "";
            string values = "";
            string zuhe = "";
            string Pk = PKName.Keys.First();
            bool ifPK = PKName.Values.First();
            foreach (var item in t.GetType().GetProperties())
            {
                if (ifPK)
                {
                    if (item.Name == Pk)
                        continue;
                }
                field = item.Name;

                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(DateTime))
                {
                    values = "'" + item.GetValue(t) + "'";
                }
                else
                {
                    if (item.PropertyType == typeof(int) || item.PropertyType == typeof(decimal) || item.PropertyType == typeof(float))
                    {
                        values = item.GetValue(t).ToString();
                    }
                }
                zuhe += field + "=" + values + ",";
            }
            zuhe = zuhe.TrimEnd(',');
            return string.Format(sql, tablename, zuhe, PKName.Keys.First(), id);
        }

        /// <summary>
        /// 修改自定义字段语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static string UptSQL<T>(Dictionary<string, bool> PKName, string tablename, int id, params SqlParameter[] paramters) where T : class,new()
        {
            string sql = "update {0} set {1} where {2}={3}";
            string field = "";
            string Pk = PKName.Keys.First();
            bool ifPK = PKName.Values.First();
                for (var i = 0; i < paramters.Length; i++)
                {
                    field += paramters[i].ParameterName + "=" + "'" + paramters[i].Value + " ',"; 
                }
                field = field.TrimEnd(',');
                T t = new T();
                return string.Format(sql, tablename, field, PKName.Keys.First(), id);
            }

































    }
}
